﻿using System.Data;
using WMSADL.SY;

namespace WMSABL
{
    public class PublicDataSet
    {
        PublicDataSetDAL dal = new PublicDataSetDAL();

        public DataSet GetDataSet(string strWhere)
        {
            return dal.GetDataSet(strWhere);
        }
        public DataSet GetDataSet(string tabName, string strWhere)
        {
            return dal.GetDataSet(tabName, strWhere);
        }
        public DataSet GetDataSet(string strfile, string table_Name, string strWhere)
        {
            return dal.GetDataSet(strfile, table_Name, strWhere);
        }
        public string GetTPermissionStr()
        {
            string str = "";
            DataSet ds = GetDataSet("DECLARE @str VARCHAR(MAX) SELECT @str = ISNULL(@str,'')+ CONVERT(nvarchar(20), [ID])+',' FROM TPermissions WHERE display=1 SELECT @str AS StrP");
            if (ds != null)
            {
                str = ds.Tables[0].Rows[0]["Strp"].ToString();
            }
            return str;
        }

        /// <summary>
        /// 执行SQL
        /// </summary>
        public void ExecuteSQL(string strWhere)
        {
            dal.ExecuteSQL(strWhere);
        }

        /// <summary>
        /// 拣货单数据导入
        /// </summary>
        public bool DataTableToSQLPickingList(DataTable dt)
        {
            return dal.DataTableToSQLPickingList(dt);
        }
        public bool DataTableToSQLPickingASPList(DataTable dt)
        {
            return dal.DataTableToSQLPickingASPList(dt);
        }

        /// <summary>
        /// 用户数据导入
        /// </summary>
        public bool DataTableToSQLUserList(DataTable dt)
        {
            return dal.DataTableToSQLUserList(dt);
        }
        public bool DataTableToSQLUserSAPList(DataTable dt)
        {
            return dal.DataTableToSQLUserSAPList(dt);
        }

        /// <summary>
        /// 仓位数据导入
        /// </summary>
        public bool DataTableToSQLPositionList(DataTable dt)
        {
            return dal.DataTableToSQLPositionList(dt);
        }
        public bool DataTableToSQLPositionSAPList(DataTable dt)
        {
            return dal.DataTableToSQLPositionSAPList(dt);
        }

        /// <summary>
        /// 库存数据导入
        /// </summary>
        public bool DataTableToSQLStockList(DataTable dt)
        {
            return dal.DataTableToSQLStockList(dt);
        }
        public bool DataTableToSQLStockSAPList(DataTable dt)
        {
            return dal.DataTableToSQLStockSAPList(dt);
        }

        /// <summary>
        /// 品部供应商数据导入
        /// </summary>
        public bool DataTableToSQLProductOrderList(DataTable dt)
        {
            return dal.DataTableToSQLProductOrderList(dt);
        }
        public bool DataTableToSQLProductOrderASPList(DataTable dt)
        {
            return dal.DataTableToSQLProductOrderASPList(dt);
        }



        #region 版本号管理
        /// <summary>
        /// 获取未赋值版本的机型数据
        /// </summary>
        /// <returns></returns>
        public DataSet GetDataSet_01_WMS(string _IP)
        {
            return dal.GetDataSet(" SELECT [Product_Model],REPLACE([Serial_Number],'%','') AS Serial_Number,BK03,BK12 FROM [DB_WMSFIRST].[dbo].[T_Stock] WHERE BK12 IS NULL AND BK03!='option' ORDER BY  BK03,[Product_Model],[Serial_Number],[NID] DESC ");
        }
        /// <summary>
        /// 机种是否经过T字工位
        /// </summary>
        /// <returns></returns>
        public string Get01_T_WorkStion(string _IP, string _ProductModel, string _Serial_Number)
        {
            DataSet ds = new DataSet();
            if (_IP.Contains("172.17") || _IP.Contains("192.168.201"))
            {
                ds = dal.GetDataSet(" SELECT TziScan_Machine AS Machine1, TziScan_Timodel AS Machine2 FROM [172.17.96.19].DB_PMSystem.dbo.TZiScan_base WHERE TziScan_Timodel='" + _ProductModel + "' AND REPLACE(TziScan_SN,'%','')= '" + _Serial_Number + "'");
            }
            else
            {
                ds = dal.GetDataSet(" SELECT TziScan_Machine AS Machine1, TziScan_Timodel AS Machine2 FROM                DB_PMSystem.dbo.TZiScan_base WHERE TziScan_Timodel='" + _ProductModel + "' AND REPLACE(TziScan_SN,'%','')= '" + _Serial_Number + "'");
            }
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            return "";
        }

        /// <summary>
        ///  是否存在版本管理
        /// </summary>
        /// <returns></returns>
        public bool Get02_Flag_Version(string _IP, string _ProductModel)
        {
            DataSet ds = new DataSet();
            ds = dal.GetDataSet(" SELECT [ProductModel] FROM [DB_WMSFIRST].[dbo].[T_Version] WHERE ProductModel='" + _ProductModel + "' ");
            if (ds.Tables[0].Rows.Count > 0)
            {
                return true;
            }
            return false;
        }
        /// <summary>
        /// 获取机种-----机芯号
        /// </summary>
        /// <returns></returns>
        public string Get03_1(string _IP, string _ProductModel, string _Serial_Number)
        {
            DataSet ds = new DataSet();
            if (_IP.Contains("172.17") || _IP.Contains("192.168.201"))
            {
                ds = dal.GetDataSet(" SELECT CASE WHEN LEN(SequencePack_order) < 12 THEN '' ELSE SUBSTRING(SequencePack_order, 1, LEN(SequencePack_order)) END AS PackingNO FROM [172.17.96.19].DB_PMSystem.dbo.SequencePack_Base WHERE SequencePack_Machine='" + _ProductModel + "' AND REPLACE(SequencePack_SN,'%','')='" + _Serial_Number + "' ");
            }
            else
            {
                ds = dal.GetDataSet(" SELECT CASE WHEN LEN(SequencePack_order) < 12 THEN '' ELSE SUBSTRING(SequencePack_order, 1, LEN(SequencePack_order)) END AS PackingNO FROM                DB_PMSystem.dbo.SequencePack_Base WHERE SequencePack_Machine='" + _ProductModel + "' AND REPLACE(SequencePack_SN,'%','')='" + _Serial_Number + "' ");
            }
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            return "";
        }
        /// <summary>
        /// 获取机种-----机芯号
        /// </summary>
        /// <returns></returns>
        public string Get03_2(string _IP, string _ProductModel, string _Serial_Number)
        {
            DataSet ds = new DataSet();
            if (_IP.Contains("172.17") || _IP.Contains("192.168.201"))
            {
                ds = dal.GetDataSet(" SELECT CASE WHEN LEN(Judgment_Str2) < 12 THEN '' ELSE SUBSTRING(Judgment_Str2, 1, LEN(Judgment_Str2)) END AS PackingNO FROM [172.17.96.19].DB_PMSystem.dbo.Judgment_Base WHERE Judgment_Machine='" + _ProductModel + "' AND REPLACE(Judgment_SN,'%','')='" + _Serial_Number + "' ");
            }
            else
            {
                ds = dal.GetDataSet(" SELECT CASE WHEN LEN(Judgment_Str2) < 12 THEN '' ELSE SUBSTRING(Judgment_Str2, 1, LEN(Judgment_Str2)) END AS PackingNO FROM DB_PMSystem.dbo.Judgment_Base WHERE Judgment_Machine='" + _ProductModel + "' AND REPLACE(Judgment_SN,'%','')='" + _Serial_Number + "' ");
            }
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            return "";
        }

        /// <summary>
        /// 获取机种-----工位时间
        /// </summary>
        /// <returns></returns>
        public string Get04(string _IP, string _ProductModel, string _MovementNO)
        {
            DataSet ds = new DataSet();
            if (_IP.Contains("172.17") || _IP.Contains("192.168.201"))
            {
                ds = dal.GetDataSet(" SELECT MIN(AB_UserTime) AS CheckTime FROM [172.17.96.19].DB_PMSystem.dbo.AN_ContinentalSea WHERE AN_Type='" + _ProductModel + "' AND AN_IDNumebr='" + _MovementNO + "' AND LEN(AN_IDNumebr)= 12  AND AN_Type!='' GROUP BY AN_Type,AN_IDNumebr ORDER BY AN_Type,AN_IDNumebr,MIN(AB_UserTime) ");
            }
            else
            {
                ds = dal.GetDataSet(" SELECT MIN(AB_UserTime) AS CheckTime FROM DB_PMSystem.dbo.AN_ContinentalSea WHERE AN_Type='" + _ProductModel + "' AND AN_IDNumebr='" + _MovementNO + "' AND  LEN(AN_IDNumebr)= 12  AND AN_Type!='' GROUP BY AN_Type,AN_IDNumebr ORDER BY AN_Type,AN_IDNumebr,MIN(AB_UserTime) ");
            }

            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            return "";
        }
        /// <summary>
        /// 获取机种-----工位时间
        /// </summary>
        /// <returns></returns>
        public string Get05(string _IP, string _ProductModel, string _MovementNO)
        {
            DataSet ds = new DataSet();
            if (_IP.Contains("172.17") || _IP.Contains("192.168.201"))
            {
                ds = dal.GetDataSet(" SELECT Access_UserTime AS CheckTime FROM [172.17.96.19].DB_PMSystem.dbo.AccessData_Base WHERE Access_Machine='" + _ProductModel + "' AND Access_order='" + _MovementNO + "' ORDER BY  Access_Machine,Access_acID,Access_UserTime ");
            }
            else
            {
                ds = dal.GetDataSet(" SELECT Access_UserTime AS CheckTime FROM DB_PMSystem.dbo.AccessData_Base WHERE Access_Machine='" + _ProductModel + "' AND Access_order='" + _MovementNO + "'  ORDER BY  Access_Machine,Access_acID,Access_UserTime ");
            }

            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            return "";
        }

        /// <summary>
        /// 获取机种-----工位时间
        /// </summary>
        /// <returns></returns>
        public string Get06(string _IP, string _ProductModel, string _SetVersionNo)
        {
            DataSet ds = new DataSet();
            if (_IP.Contains("172.17") || _IP.Contains("192.168.201"))
            {
                ds = dal.GetDataSet(" SELECT Tzi_Datetime AS CheckTime FROM [172.17.96.19].DB_PMSystem.dbo.Tzi_Base WHERE Tzi_ShiMachine!='' AND Tzi_ShiMachine='" + _ProductModel + "' AND Tzi_SN='" + _SetVersionNo + "' ORDER BY  Tzi_ShiMachine,Tzi_SN,Tzi_Datetime ");
            }
            else
            {
                ds = dal.GetDataSet(" SELECT  Tzi_Datetime AS CheckTime FROM DB_PMSystem.dbo.Tzi_Base WHERE Tzi_ShiMachine!='' AND Tzi_ShiMachine='" + _ProductModel + "' AND Tzi_SN='" + _SetVersionNo + "'  ORDER BY  Tzi_ShiMachine,Tzi_SN,Tzi_Datetime ");
            }

            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            return "";
        }

        /// <summary>
        /// 获取机种-----工位时间(PROAV)
        /// </summary>
        /// <returns></returns>
        public string Get07_PROAV(string _IP, string _ProductModel, string _SetVersionNo)
        {
            DataSet ds = new DataSet();
            if (_IP.Contains("172.17") || _IP.Contains("192.168.201"))
            {
                ds = dal.GetDataSet(" SELECT  StartDateTime AS CheckTime  FROM [172.17.96.19,2433\\PROAV].[TDB_Shipping].[dbo].[proav_product] WHERE  ShippedModelName='" + _ProductModel + "' AND Serial='" + _SetVersionNo + "' ORDER BY StartDateTime ASC");
            }
            else
            {
                ds = dal.GetDataSet(" SELECT  CheckTime  FROM proav_product  WHERE  ShippedModelName='" + _ProductModel + "' AND Serial='" + _SetVersionNo + "'  ORDER BY CheckTime ASC");
            }
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            return "";
        }

        /// <summary>
        /// 取机种BOM中1个部品
        /// </summary>
        /// <returns></returns>
        public string Get08_BOM(string _IP, string _ProductModel)
        {
            DataSet ds = new DataSet();
            ds = dal.GetDataSet(" SELECT TOP 1 MAX(A.ProductNo) ProductNo FROM ERun_9_SAP.dbo.MA04_Produce AS B LEFT OUTER JOIN ERun_9_SAP.dbo.MA04_Produce_Detail AS A ON A.BillNo = B.BillNo WHERE A.EDate>GETDATE() AND B.ProductNo='" + _ProductModel + "'  GROUP BY B.ProductNo HAVING COUNT(B.ProductNo)>1   ORDER BY  B.ProductNo DESC ");

            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            return "";
        }

        /// <summary>
        /// 部品最近的发料时间
        /// </summary>
        /// <returns></returns>
        public string Get09_PickingINFO(string _IP, string _ProductNO, string _CheckTime)
        {
            DataSet ds = new DataSet();
            ds = dal.GetDataSet(" SELECT TOP 1 '20'+SUBSTRING([PKNo], 1, LEN([PKNo]) - 3)AS GetTime FROM [ERun_9_SAP].[dbo].[PDA_Obtain] WHERE  UserCode NOT LIKE 'SMT%' AND ProductNo='" + _ProductNO + "' AND ScanTime<'" + _CheckTime + "'  ORDER BY  [ScanTime] DESC ");

            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            return "";
        }

        /// <summary>
        /// 对比版本基础表得到此序列号的版本号
        /// </summary>
        /// <returns></returns>
        public string Get10_Version(string _IP, string _ProductNO, string _CheckTime)
        {
            DataSet ds = new DataSet();
            ds = dal.GetDataSet(" SELECT VersionNum FROM [DB_WMSFIRST].[dbo].[T_Version] where ProductModel='" + _ProductNO + "'  AND CAST('" + _CheckTime + "' AS DATETIME) >= CAST([StartTime] AS DATETIME) AND CAST('" + _CheckTime + "' AS DATETIME) <=  CAST([EndTime] AS DATETIME)  ");

            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            return "";
        }
        #endregion
    }
}
